﻿using CallCenter.Data.Base;
using CallCenter.Data.BizUtilities.Base;
using CallCenter.Data.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace CallCenter.Data.BizUtilities
{
    public class ProjectSubTaskUtility : BaseUtility
    {
        public ProjectSubTaskUtility(DataManager manager)
            : base(manager)
        { }

        private static DateTime orginalTaskStartDate;
        private static DateTime orginalTaskEndDate;
        public ReturnMessage<SubTaskViewModel> InsertToSubTask(SubTaskViewModel model)
        {
            var returnMessage = new ReturnMessage<SubTaskViewModel>();
            //  if(this.ValidateScratchPadModel(model))
            //   {   

            if (String.IsNullOrEmpty(model.notes))
                model.notes = "";


            DataRow dr = this.OriginalTaskDate(model.taskId);
            DateTime orginalStartDate = DateTime.Parse(dr["startDate"].ToString());
            DateTime orginalEndDate = DateTime.Parse(dr["endDate"].ToString());
            if (model.startDate.HasValue)
            {

                if (model.startDate.Value.DayOfWeek == DayOfWeek.Saturday)
                    model.startDate = model.startDate.Value.AddDays(2);
                else if (model.startDate.Value.DayOfWeek == DayOfWeek.Sunday)
                    model.startDate = model.startDate.Value.AddDays(1);

            }

            if (model.startDate.HasValue)
            {
                //compare the start date with start date of the parent task
                if (model.startDate.Value.CompareTo(orginalTaskStartDate) < 0)
                    model.startDate = orginalStartDate;
                else if (model.startDate.Value.CompareTo(orginalTaskEndDate) > 0)
                    model.startDate = orginalStartDate;
            }
            #region get only weekdays and add it to duration


            if (model.noOfDays != 0)
            {
                model.endDate = AddBusinessDays(model.startDate, model.noOfDays);
            }
            else
                model.endDate = model.startDate;

            if (model.endDate.HasValue)
            { 
                if (model.endDate.Value.CompareTo(orginalEndDate) > 0) // check if the end date is greater than the end date of the parent end date
                {
                    model.endDate = orginalEndDate;     // the end date of the parent task
                }
        }

            #endregion

            int id = 0;
            if (model.taskName != "")
            {
                if (model.heldById != 0)
                    id = this.Insert(model.taskId, model.name, model.startDate, model.endDate, model.statusId, model.noOfDays, model.gap, model.heldById, model.notes);
                returnMessage.Message = "Record has been inserted";
                returnMessage.Status = true;
            }
            else
                returnMessage.Message = "Record can not be inserted, Task name or Held By cannot be empty";                          
            return returnMessage;
       }

        public ReturnMessage<SubTaskViewModel> UpdateToSubTask(SubTaskViewModel model)
        {
            var returnMessage = new ReturnMessage<SubTaskViewModel>();
            //  if(this.ValidateScratchPadModel(model))
            //   {


            if (String.IsNullOrEmpty(model.notes))
                model.notes = "";
            if (model.gap == 0)
                model.gap = 0;
            DataRow dr = this.OriginalTaskDate(model.taskId);
            DateTime orginalStartDate = DateTime.Parse(dr["startDate"].ToString());
            DateTime orginalEndDate = DateTime.Parse(dr["endDate"].ToString());

            if (!model.startDate.HasValue)
            {
                if ((model.startDate.Value.CompareTo(DateTime.MinValue) == 0))
                {
                    model.startDate = orginalStartDate;
                    returnMessage.Message = "Date cannot be updated";
                }
            }
            if (!model.startDate.HasValue)
            {

                if (model.startDate.Value.CompareTo(orginalStartDate) < 0)
                {
                    model.startDate = orginalStartDate;
                    returnMessage.Message = "Date cannot be updated. Check Dates for parent task";
                }
                else if (model.startDate.Value.CompareTo(orginalEndDate) > 0)
                {
                    model.startDate = orginalStartDate;
                    returnMessage.Message = "Date cannot be updated. Check Dates for parent task";
                }

            }
            #region get only weekdays and add it to duration

            if (model.startDate.HasValue)
            {
                DateTime TempendDate = model.startDate.Value.AddDays(model.noOfDays);
            }
           
            if (model.noOfDays != 0)
            {
               model.endDate = AddBusinessDays(model.startDate, model.noOfDays);
            }
            else
                model.endDate = model.startDate;

            if (model.endDate.HasValue)
            {
                if (model.endDate.Value.CompareTo(orginalEndDate) > 0) // check if the end date is greater than the end date of the parent end date
                {
                    model.endDate = orginalEndDate;     // the end date of the parent task

                }
            }

            #endregion
            int id = 0;
            if (model.taskName != "")
            {
                if (model.heldById != 0)
                {
                    id = this.Update(model.id, model.taskId, model.name, model.startDate, model.endDate, model.statusId, model.noOfDays, model.gap, model.heldById, model.notes);
                    if (id > 0)
                    {
                        returnMessage.Message = "Record has been inserted";
                        returnMessage.Status = true;
                    }

                    else
                    {
                        returnMessage.Message = "Record can not be Updated, task name or held By connect be empty";
                        returnMessage.Status = false;
                    }
                }

                else
                {
                    returnMessage.Message = "Record can not be Updated, task name or held By connect be empty";
                    returnMessage.Status = false;
                }
            }

            else {
                returnMessage.Message = "Record can not be Updated, task name or held By connect be empty";
                returnMessage.Status = false;
            }
            return returnMessage;
        }
       

        public int Insert(int taskId, string name, Nullable<DateTime> startDate, Nullable<DateTime> endDate, int statusId,
                           int noOfDays, int gap, int heldById, string notes)
        {
            int nRtn = -1;

         string sql = "Insert INTO ccSubTask " +
                               "(taskId,name, startDate,endDate,noOfDays," +
                               "gap,heldById,statusId,notes) " +
                               "Values(@taskId,@name, @startDate,@endDate,@noOfDays," +
                               "@gap,@heldById,@statusId,@notes) SELECT @pResult =@@IDENTITY";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                //this.Factory.AddParameter(cmd, "@startDate", SqlDbType.SmallDateTime, 10, startDate);
                //this.Factory.AddParameter(cmd, "@endDate", SqlDbType.SmallDateTime, 10, endDate);
                this.Factory.AddParameter(cmd, "@taskId", SqlDbType.Int, 10, taskId);
                this.Factory.AddParameter(cmd, "@name", SqlDbType.NVarChar, 100, name);
                this.Factory.AddParameter(cmd, "@noOfDays", SqlDbType.Int, 10, noOfDays);
                this.Factory.AddParameter(cmd, "@gap", SqlDbType.Int, 10, gap);
                this.Factory.AddParameter(cmd, "@heldById", SqlDbType.Int, 10, heldById);
                this.Factory.AddParameter(cmd, "@statusId", SqlDbType.Int, 10, statusId);
               this.Factory.AddParameter(cmd, "@notes", SqlDbType.NVarChar, 1000, notes);


                if ((startDate == null) || (startDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, startDate);

                if ((endDate == null) || (endDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@endDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@endDate", SqlDbType.DateTime, 10, endDate);

                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }
            return nRtn;

        }

        public int Update(int id, int taskId, string name, Nullable<DateTime> startDate, Nullable<DateTime> endDate, int statusId,
                        int noOfDays, int gap, int heldById, string notes)
        {
            int nRtn = -1;

        string sql= "UPDATE ccSubTask SET taskId=@taskId,name=@name," +
                                    "startDate=@startDate,endDate=@endDate,noOfDays=@noOfDays,statusId=@statusId," +
                                    "gap=@gap,heldById=@heldById,notes=@notes" +
                                    " WHERE id = @ID";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                //this.Factory.AddParameter(cmd, "@startDate", SqlDbType.SmallDateTime, 10, startDate);
                //this.Factory.AddParameter(cmd, "@endDate", SqlDbType.SmallDateTime, 10, endDate);
                this.Factory.AddParameter(cmd, "@taskId", SqlDbType.Int, 10, taskId);
                this.Factory.AddParameter(cmd, "@name", SqlDbType.NVarChar, 100, name);
                this.Factory.AddParameter(cmd, "@noOfDays", SqlDbType.Int, 10, noOfDays);
                this.Factory.AddParameter(cmd, "@gap", SqlDbType.Int, 10, gap);
                this.Factory.AddParameter(cmd, "@heldById", SqlDbType.Int, 10, heldById);
                this.Factory.AddParameter(cmd, "@statusId", SqlDbType.Int, 10, statusId);

                this.Factory.AddParameter(cmd, "@notes", SqlDbType.NVarChar, 1000, notes);
                this.Factory.AddParameter(cmd, "@id", SqlDbType.Int, 10, id);

                if ((startDate == null) || (startDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, startDate);

                if ((endDate == null) || (endDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@endDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@endDate", SqlDbType.DateTime, 10, endDate);
                this.Factory.ExecuteNonQuery(cmd);

            }
            nRtn = id;
            return nRtn; //retruning the data stored in the table
        }


      
        public int UpdateDate(int id, Nullable<DateTime> startDate, Nullable<DateTime> endDate)
        {
            int nRtn = -1;

          string sql= "UPDATE ccSubTask SET startDate=@startDate,endDate=@endDate" +
                                    " WHERE id = @ID";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                //this.Factory.AddParameter(cmd, "@startDate", SqlDbType.SmallDateTime, 10, startDate);
                //this.Factory.AddParameter(cmd, "@endDate", SqlDbType.SmallDateTime, 10, endDate);

                this.Factory.AddParameter(cmd, "@id", SqlDbType.Int, 10, id);
                if ((startDate == null) || (startDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, startDate);

                if ((endDate == null) || (endDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@endDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@endDate", SqlDbType.DateTime, 10, endDate);
                this.Factory.ExecuteNonQuery(cmd);

            }
            nRtn = id;
            return nRtn; //retruning the data stored in the table
        }

        protected void updateDependedSubTasks(int parentId, DateTime startDate, DateTime endDate)
        {

            DataTable dt = this.Bridge.ProjectSubTaskUtility.GetAllGrids(parentId);

            if (dt.Rows.Count != 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    int id = Convert.ToInt16(dt.Rows[i]["id"].ToString());
                    DateTime sDate = startDate;
                    DateTime nDate = endDate;
                    int noOfDays = Convert.ToInt32(dt.Rows[i]["NoOfDays"].ToString());
                    int g = Convert.ToInt32(dt.Rows[i]["gap"].ToString());
                    #region get only weekdays and add it to duration
                    nDate = AddBusinessDays(sDate, noOfDays);

                    #endregion
                    if (nDate.CompareTo(endDate) < 0)
                        this.Bridge.ProjectSubTaskUtility.UpdateDate(id, sDate, nDate); //update only start date and the end date of the subtask
                    else
                        this.Bridge.ProjectSubTaskUtility.UpdateDate(id, sDate, endDate);

                }
            }

        }

        public List<SubTaskViewModel> GetAllGrid(int taskId)
        { 
   string sql = "SELECT st.*, c.lastname +' '+ c.firstname as heldContactName, t.taskName, s.description as statusName " +
                                    "FROM ccSubTask st " +
                                    "INNER JOIN Tasks t ON t.id = st.taskId " +
                                    "INNER JOIN ccContacts c ON c.id = st.heldById " +
                                    "INNER JOIN ddlEmployees e ON e.contactId = c.id " +
                                    "INNER JOIN ddlStatus s ON s.id = st.statusId " +
                                    "WHERE st.taskId = " + taskId;

            return this.Factory.ExecuteObjects<SubTaskViewModel>(sql);

        }


        public DataTable GetAllGrids(int taskId)
        {
            string sql = "SELECT st.*, c.lastname+','+c.firstname as heldContactName, t.taskName, s.description as statusName " +
                                             "FROM ccSubTask st " +
                                             "INNER JOIN Tasks t ON t.id = st.taskId " +
                                             "INNER JOIN ccContacts c ON c.id = st.heldById " +
                                             "INNER JOIN ddlEmployees e ON e.contactId = c.id " +
                                             "INNER JOIN ddlStatus s ON s.id = st.statusId " +
                                             "WHERE st.taskId = " + taskId;

            return this.Factory.ExecuteTable(sql);

        }

        public void Delete(int id) //delete the row of data using the id passed to the function
        {string sql= "DELETE FROM ccSubTask " +
                "WHERE ID = @ID ";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, id);
                this.Factory.ExecuteNonQuery(cmd);
            }
        
        }
        public DataRow OriginalTaskDate(int taskId)
        {

            string sql = " SELECT * FROM Tasks " +
                                      " WHERE id = " + taskId;

            return this.Factory.ExecuteRecord(sql);
    
        //if (dt.Rows.Count == 1)
        //        return dt.Rows[0];
        //    else
        //        return null;


        }

        public static DateTime AddBusinessDays(Nullable<DateTime> startDate, int days)
        {
            if (days == 0) return startDate.Value;

            if (startDate.Value.DayOfWeek == DayOfWeek.Saturday)
            {
                startDate = startDate.Value.AddDays(2);
                days -= 1;
            }
            else if (startDate.Value.DayOfWeek == DayOfWeek.Sunday)
            {
                startDate = startDate.Value.AddDays(1);
                days -= 1;
            }

            startDate = startDate.Value.AddDays(days / 5 * 7);
            int extraDays = days % 5;

            if ((int)startDate.Value.DayOfWeek + extraDays > 5)
            {
                extraDays += 2;
            }

            return startDate.Value.AddDays(extraDays);

        }




    }




}
